package com.wuseguang.springcloud.service.impl;

import com.wuseguang.springcloud.dao.jdbc.ImportAndExportSchoolsJdbc;
import com.wuseguang.springcloud.dao.mapper.ImportAndExportSchoolsMapper;
import com.wuseguang.springcloud.entity.ImportAndExportSchools;
import com.wuseguang.springcloud.service.ImportAndExportSchoolsService;
import com.wuseguang.springcloud.util.Result;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * <p>
 * 学生进出校表 服务实现类
 * </p>
 *
 * @author
 * @since 2020-03-29
 */
@Service
public class ImportAndExportSchoolsServiceImpl implements ImportAndExportSchoolsService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private ImportAndExportSchoolsJdbc importAndExportSchoolsJdbc;

    @Override
    public Result findStudentInAndOut(Integer classId) {

        //查询进出校信息
        String sql = "select * from (" +
                " select s.id,s.student_name,s.head_portrait,iaes.type,DATE_FORMAT(iaes.time,'%Y-%m-%d %T') time from student s " +
                " left join import_and_export_schools iaes on(s.id=iaes.student_id) " +
                " where s.class_id =? ORDER BY iaes.id desc ) s GROUP BY s.id";
        List<Map<String, Object>> list = importAndExportSchoolsJdbc.queryListMap(sql, classId);
        return new Result(list);
    }

    @Override
    public Result findInAndOutStatistics(String schoolId) {

        //查询这个学校的所有班级
        String sql = "select c.id,c.`name` from classes c  where c.school_id = ? and not exists(select * from grade g where g.id=c.grade_id and g.other=1)";
        List<Map<String, Object>> classList = importAndExportSchoolsJdbc.queryListMap(sql, schoolId);

        //全校进校
        Integer enter = 0;
        //全校离校
        Integer leave = 0;

        //循环班级信息
        for (Map<String, Object> map : classList) {
            logger.info("循环班级信息 班级id = {},班级名称 = {}", map.get("id"), map.get("name"));

            sql = "select IFNULL(SUM(CASE WHEN type =1 or type is null THEN 1 ELSE 0 END),0) AS `leave` ," +
                    " IFNULL(SUM(CASE WHEN type =0 THEN 1 ELSE 0 END),0) as `enter` from student s " +
                    " LEFT JOIN import_and_export_schools iaes on(s.id=iaes.student_id and iaes.id in " +
                    "(select  max(iaes.id) from student s RIGHT JOIN import_and_export_schools iaes " +
                    "on(s.id=iaes.student_id) where s.class_id = ?  GROUP BY s.id ) ) where s.class_id = ? ";

            Map<String, Object> elMap = null;
            try {
                elMap = importAndExportSchoolsJdbc.queryMap(sql, map.get("id").toString(), map.get("id").toString());
            } catch (Exception e) {
                logger.error("e", e);
                elMap = new HashMap<>();
                elMap.put("enter", "0");
                elMap.put("leave", "0");
            }

            //全校进校
            enter += Integer.valueOf(elMap.get("enter").toString());
            //全校离校
            leave += Integer.valueOf(elMap.get("leave").toString());
            map.put("elMap",elMap);
        }

        //返回信息
        Map<String, Object> returnMap = new HashMap<String, Object>();
        //全校进校
        returnMap.put("enter", enter);
        //全校离校
        returnMap.put("leave", leave);
        //全校人数
        returnMap.put("total", leave + enter);
        //班级进离校信息
        returnMap.put("classList", classList);
        return new Result(returnMap);
    }
}
